<?php


namespace app\web\controller;


use app\web\model\Comments;
use http\Params;
use think\Controller;
use think\Db;
use think\facade\Request;

class Ksh extends Controller
{
    public function datacount()
    {
        return Comments::count();
    }

    public function keywordscount()
    {
        $res = Db::query('select count(w.word) as c from (select count(*) as word from comments group by KeyWords)as w');
        return $res;
    }

    public function predict()
    {
        $done = Comments::where('Theme', '')->count();
        $total = Comments::count();
        return round(($total - $done) / $total * 100, 2);
    }

    public function manyi()
    {
        $pos = Comments::where('Emotional', '满意')->count();
        $total = Comments::count();
        return round($pos / $total * 100, 2) . '%';
    }

    public function themepie()
    {
        return json(Db::query('select Theme as name,count(Theme) as value from comments group by Theme'));
    }

    public function themepieupdate()
    {
        $model = Request::param('model');
        return json(Db::query("select Theme as name,count(Theme) as value  from comments where CarModel like '%" . $model . "%' group by Theme"));
    }


    public function emot()
    {
        $year = Db::query("select date_format(ReleaseDate,'%Y') as year from comments where Emotional='不满意' group by year");
        $data1 = Db::query("select count(ReleaseDate) as num from comments where Emotional='满意' group by date_format(ReleaseDate,'%Y')");
        $data2 = Db::query("select count(ReleaseDate) as num from comments  group by date_format(ReleaseDate,'%Y')");
        $arr = array();
        $arr1 = array();
        foreach ($year as $item) {
            array_push($arr1, $item['year']);
        }
        for ($i = 0; $i < count($data1); $i++) {
            array_push($arr, round($data1[$i]['num'] / $data2[$i]['num'] * 100, 2));
        }
        return json(array($arr1, $arr));
    }

    public function emotupdate()
    {
        $model = Request::param('model');
        $year = Db::query("select date_format(ReleaseDate,'%Y') as year from comments where Emotional='不满意' and CarModel like '%" . $model . "%' group by year");
        $data1 = Db::query("select count(ReleaseDate) as num from comments where Emotional='满意' and CarModel like '%" . $model . "%' group by date_format(ReleaseDate,'%Y')");
        $data2 = Db::query("select count(ReleaseDate) as num from comments where CarModel like '%" . $model . "%' group by date_format(ReleaseDate,'%Y')");
        $arr = array();
        $arr1 = array();
        foreach ($year as $item) {
            array_push($arr1, $item['year']);
        }
        for ($i = 0; $i < count($data1); $i++) {
            array_push($arr, round($data1[$i]['num'] / $data2[$i]['num'] * 100, 2));
        }
        return json(array($arr1, $arr));
    }

    public function negcontent()
    {
        $year = Db::query("select date_format(ReleaseDate,'%Y') as year from comments where Emotional='不满意' group by year");
        $data1 = Db::query("select count(ReleaseDate) as num from comments where Emotional='不满意' group by date_format(ReleaseDate,'%Y')");
        $arr1 = array();
        $arr2 = array();
        $arr3 = array();
        foreach ($year as $item) {
            array_push($arr1, $item['year']);
        }
        foreach ($data1 as $item) {
            array_push($arr2, $item['num']);
        }
        $arr = array($arr1, $arr2);
        return json($arr);
    }

    public function negcontentupdate()
    {
        $model = Request::param('model');
        $year = Db::query("select date_format(ReleaseDate,'%Y') as year from comments where Emotional='不满意' and CarModel like '%" . $model . "%' group by year");
        $data1 = Db::query("select count(ReleaseDate) as num from comments where Emotional='不满意' and CarModel like '%" . $model . "%' group by date_format(ReleaseDate,'%Y')");
        $arr1 = array();
        $arr2 = array();
        foreach ($year as $item) {
            array_push($arr1, $item['year']);
        }
        foreach ($data1 as $item) {
            array_push($arr2, $item['num']);
        }
        $arr = array($arr1, $arr2);
        return json($arr);
    }

    public function comnum()
    {
        return Comments::count();
    }

    public function comnumupdate()
    {
        $model = Request::param('model');
        $theme = Request::param('theme');
        if ($model == "all" and $theme == "all")
            return Comments::count();
        elseif ($model == "all")
            return Comments::where('Theme', $theme)->count();
        elseif ($theme == "all")
            return Comments::where('CarModel', "like", "%" . $model . "%")->count();
        else
            return Comments::where('CarModel', "like", "%" . $model . "%")->where('Theme', $theme)->count();

    }

    public function wordsnum()
    {
        return Db::query("select count(k) as num from (select count(KeyWords) as k from comments group by KeyWords) as n");
    }

    public function wordsnumupdate()
    {
        $model = Request::param('model');
        $theme = Request::param('theme');
        if ($model == "all" and $theme == "all")
            return Db::query("select count(k) as num from (select count(KeyWords) as k from comments group by KeyWords) as n");
        elseif ($model == "all")
            return Db::query("select count(k) as num from (select count(KeyWords) as k from comments where Theme='" . $theme . "' group by KeyWords) as n");
        elseif ($theme == "all")
            return Db::query("select count(k) as num from (select count(KeyWords) as k from comments where CarModel like '%" . $model . "%' group by KeyWords) as n");
        else
            return Db::query("select count(k) as num from (select count(KeyWords) as k from comments where CarModel like '%" . $model . "%' and Theme='" . $theme . "' group by KeyWords) as n");


    }

    public function acc()
    {
        $total = Comments::count();
        $good = Comments::where('Emotional')->count();
        if ($good / $total > 0.5)
            return "满意";
        else
            return "不满意";
    }

    public function accupdate()
    {
        $model = Request::param('model');
        $theme = Request::param('theme');
        if ($model == "all" and $theme == "all") {
            $total = Comments::count();
            $good = Comments::where('Emotional', '满意')->count();
        } elseif ($model == "all") {
            $total = Comments::where('Theme', $theme)->count();
            $good = Comments::where('Theme', $theme)->where('Emotional', '满意')->count();
        } elseif ($theme == "all") {
            $total = Comments::where('CarModel', "like", "%" . $model . "%")->count();
            $good = Comments::where('CarModel', "like", "%" . $model . "%")->where('Emotional', '满意')->count();
        } else {
            $total = Comments::where('Theme', $theme)->where('CarModel', "like", "%" . $model . "%")->count();
            $good = Comments::where('Theme', $theme)->where('CarModel', "like", "%" . $model . "%")->where('Emotional', '满意')->count();
        }
        if ($good / $total > 0.5)
            return "满意";
        else
            return "不满意";
    }


    public function wordsCloudInit()
    {
        $sql = "select KeyWords,COUNT(KeyWords) as count from comments WHERE KeyWords is NOT null GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        return Db::query($sql);
    }

    public function wordsCloudUpdate()
    {
        $model = Request::param('model');
        $theme = Request::param('theme');
        if ($model == "all" and $theme == "all")
            $sql = "select KeyWords,COUNT(KeyWords) as count from comments WHERE KeyWords is NOT null GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        elseif ($model == "all")
            $sql = "select KeyWords,COUNT(KeyWords) as count from comments where Theme='" . $theme . "' GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        elseif ($theme == "all")
            $sql = "select KeyWords,COUNT(KeyWords) as count from comments where CarModel like '%" . $model . "%' GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
        else
            $sql = "select KeyWords,COUNT(KeyWords) as count from comments where CarModel like '%" . $model . "%' and Theme ='" . $theme . "' GROUP BY KeyWords ORDER BY count DESC LIMIT 100";
//        return json(Db::query($sql));
        return Db::query($sql);
    }
}